﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.Collections.Specialized;
using PDOGenerator.MapTypes;

namespace PDOGenerator.Generators
{

    using SMO = Microsoft.SqlServer.Management.Smo;

    public class SQLScriptGenerator
    {
        private SMO.Server _srv;
        private SMO.Database _db;
        private SMO.Scripter _scripter;
        private bool _targetAzure;

        /// <summary>
        /// Build table create scripts, etc.
        /// </summary>
        public SQLScriptGenerator(string serverName, string databaseName, bool targetAzure)
        {
            _srv = new SMO.Server(serverName);
            _db = _srv.Databases[databaseName];
            _scripter = new SMO.Scripter(_srv);
            _targetAzure = targetAzure;

            SetDefaultOptions();
        }

        private void SetDefaultOptions()
        {
            _scripter.Options.ScriptDrops = false;
            _scripter.Options.WithDependencies = false;
            _scripter.Options.DriAll = true;
            _scripter.Options.Default = true;
            _scripter.Options.ExtendedProperties = true;
            _scripter.Options.AllowSystemObjects = false;
            _scripter.Options.SchemaQualifyForeignKeysReferences = true;
            _scripter.Options.ScriptDrops = false; // scripting create, not drop

            if (_targetAzure)
            {
                // Azure options
                _scripter.Options.AnsiPadding = false;
                _scripter.Options.NoIndexPartitioningSchemes = true;
                _scripter.Options.NoTablePartitioningSchemes = true;
                _scripter.Options.ExtendedProperties = false; // opposite value from above
            }
        }


        private string PostProcessFixup(string script)
        {
            if (_targetAzure)
            {
                string s2 = script.Replace("SET ANSI_NULLS ON", "");
                s2 = s2.Replace("PAD_INDEX  = OFF, ", "");
                s2 = s2.Replace(", ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON", "");
                s2 = s2.Replace("ON [PRIMARY]", "");
                return s2;
            }
            else
            {
                return script;
            }
        }

        /// <summary>
        /// Generate table create scripts for all tables in current database.
        /// </summary>
        /// <returns>script</returns>
        public string GenerateTableScripts()
        {
            SetDefaultOptions();

            StringBuilder sb = new StringBuilder();

            foreach (SMO.Table t in _db.Tables)
            {
                if (!t.IsSystemObject)
                {
                    Urn[] smoObjects = new Urn[1] { t.Urn };
                    sb.Append(ConcatStrings(_scripter.Script(smoObjects)));
                }
            }

            return PostProcessFixup(sb.ToString());
        }


        /// <summary>
        /// Generate script for a provided schema by name.
        /// </summary>
        /// <param name="schemaName">name of schema</param>
        /// <returns>script</returns>
        public string GenerateSchemaScript(string schemaName)
        {
            SetDefaultOptions();

            Urn[] smoObjects = null;
            if (null == schemaName)
            {
                List<Urn> urnList = new List<Urn>();
                foreach (SMO.Schema s in _db.Schemas)
                {
                    urnList.Add(s.Urn);
                }
                smoObjects = urnList.ToArray();
            }
            else
            {
                SMO.Schema schema = _db.Schemas[schemaName];
                smoObjects = new Urn[1] { schema.Urn };
            }
            return PostProcessFixup(ConcatStrings(_scripter.Script(smoObjects)));
        }

        /// <summary>
        /// Generate script for a provided schema by name.
        /// </summary>
        /// <param name="subProjectName">sub project name</param>
        /// <returns>script</returns>
        public string GenerateSubProjectSchemaScript(Project p, string subProjectName)
        {
            SetDefaultOptions();
            _scripter.Options.IncludeIfNotExists = true;

            Urn[] smoObjects = null;
            List<Urn> urnList = new List<Urn>();
            foreach (SchemaMap sm in p.SchemaList)
            {
                if (sm.SubProjectName == subProjectName)
                {
                    SMO.Schema schema = _db.Schemas[sm.SchemaName];
                    urnList.Add(schema.Urn);
                }
            }
            smoObjects = urnList.ToArray();
            return PostProcessFixup(ConcatStrings(_scripter.Script(smoObjects)));
        }



        /// <summary>
        /// Generate data insert script.
        /// </summary>
        /// <param name="subProjectName">sub project name</param>
        /// <returns>script</returns>
        public string GenereateDataScript(Project p, string subProjectName)
        {
            SMO.Scripter dataScripter = new Scripter(_srv);
            dataScripter.Options.ScriptSchema = false;
            dataScripter.Options.ScriptData = true;

            StringBuilder sb = new StringBuilder();

            bool noSubProjectData = true;
            foreach (SchemaMap sm in p.SchemaList)
            {
                if (sm.SubProjectName == subProjectName)
                {
                    foreach (TableMap tm in sm.TableList)
                    {
                        if (!tm.IsView)
                        {
                            SMO.Table t = _db.Tables[tm.TableName, tm.SchemaName];
                            Urn[] smoObjects = new Urn[1] { t.Urn };

                            bool noTableData = true;
                            foreach (string s in dataScripter.EnumScript(smoObjects))
                            {
                                if (noSubProjectData)
                                {
                                    // Disable all check constraints before adding data
                                    sb.Append("EXEC sp_msforeachtable \"ALTER TABLE ? NOCHECK CONSTRAINT all\"");
                                    noSubProjectData = false;
                                }
                                if (noTableData)
                                {
                                    sb.Append(Environment.NewLine + "--" + t.Name + Environment.NewLine);
                                    noTableData = false;
                                }
                                string s2 = s.Replace(") VALUES (", ")" + Environment.NewLine + "VALUES (");
                                sb.Append(s2);
                            }

                            if (!noTableData)
                            {
                                sb.Append(Environment.NewLine + "GO" + Environment.NewLine);
                            }

                        }
                    }
                }
            }

            if (!noSubProjectData)
            {
                // Re-enable check constraints 
                sb.Append("EXEC sp_msforeachtable \"ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all\"");
            }

            return sb.ToString();
        }



        /// <summary>
        /// Generate script for a provided table by name.
        /// </summary>
        /// <param name="schemaName">name of schema</param>
        /// <param name="tableName">name of table</param>
        /// <returns>script</returns>
        public string GenerateTableScript(string schemaName, string tableName)
        {
            return GenerateTableScript(schemaName, tableName, false);
        }

        /// <summary>
        /// Generate script for a provided table by name.
        /// </summary>
        /// <param name="schemaName">name of schema</param>
        /// <param name="tableName">name of table</param>
        /// <param name="drop">true to generate table drop, false for table create</param>
        /// <returns>script</returns>
        public string GenerateTableScript(string schemaName, string tableName, bool drop)
        {
            SetDefaultOptions();
            _scripter.Options.ScriptDrops = drop;
            ///// CX ?? _scripter.Options.WithDependencies = drop;

            SMO.Table t = _db.Tables[tableName, schemaName];
            Urn[] smoObjects = new Urn[1] { t.Urn };

            return PostProcessFixup(ConcatStrings(_scripter.Script(smoObjects)));
        }


        /// <summary>
        /// Generate scripts for all tables in provided schema name.
        /// </summary>
        /// <param name="schemaName">schema name</param>
        /// <param name="drop">true to generate table drop, false for table create</param>
        /// <returns>script</returns>
        public string GenerateTableScript(string schemaName, bool drop)
        {
            SetDefaultOptions();
            _scripter.Options.ScriptDrops = drop;

            List<Urn> urnList = new List<Urn>();
            foreach (SMO.Table t in _db.Tables)
            {
                if (!t.IsSystemObject && (null == schemaName || t.Schema == schemaName))
                {
                    urnList.Add(t.Urn);
                }
            }

            return PostProcessFixup(ConcatStrings(_scripter.Script(urnList.ToArray())));
        }



        /// <summary>
        /// Generate scripts for all tables in provided schema name.
        /// </summary>
        /// <param name="schemaName">schema name</param>
        /// <param name="drop">true to generate table drop, false for table create</param>
        /// <returns>script</returns>
        public string GenerateSubProjectTableScript(Project p, string subProjectName, bool drop)
        {
            SetDefaultOptions();
            _scripter.Options.ScriptDrops = drop;

            List<Urn> urnList = new List<Urn>();

            foreach (SchemaMap sm in p.SchemaList)
            {
                if (sm.SubProjectName == subProjectName)
                {
                    foreach (TableMap tm in sm.TableList)
                    {
                        if (!tm.IsView)
                        {
                            SMO.Table t = _db.Tables[tm.TableName, tm.SchemaName];
                            urnList.Add(t.Urn);
                        }
                    }
                }
            }
            return PostProcessFixup(ConcatStrings(_scripter.Script(urnList.ToArray())));

            //foreach (SMO.Table t in _db.Tables)
            //{
            //    if (!t.IsSystemObject && (null == schemaName || t.Schema == schemaName))
            //    {
            //        urnList.Add(t.Urn);
            //    }
            //}

            //return ConcatStrings(_scripter.Script(urnList.ToArray()));
        }


        private string ConcatStrings(StringCollection sc)
        {
            StringBuilder sb = new StringBuilder();
            foreach (string s in sc)
            {
                sb.Append(s);
                sb.Append(Environment.NewLine + "GO" + Environment.NewLine);
            }
            return sb.ToString();
        }
    }
}
